Excel BI - Excel Challenge 655

excel-challenges

excel-formulas

🔰 Find whether a sequence is increasing (I), decreasing (D) or neither of these (N).

Published

March 24, 2026

Illustration for Excel BI - Excel Challenge 655

Challenge Description

🔰 Find whether a sequence is increasing (I), decreasing (D) or neither of these (N).

Solutions

library(tidyverse)
library(readxl)

path = "Excel/655 Increasing or Decreasing or None Sequences.xlsx"
input = read_excel(path, range = "A1:A8")
test  = read_excel(path, range = "B1:B8")

result = input %>%
  mutate(rn = row_number()) %>%
  separate_rows(Sequences, sep = ",", convert = TRUE) %>%
  mutate(diff = Sequences - lag(Sequences), .by = rn) %>%
  na.omit() %>%
  summarise(`Answer Expected` = case_when(all(diff > 0) ~ "I",
                               all(diff < 0) ~ "D",
                               TRUE ~ "N"), .by = rn) %>%
  select(-rn)

all.equal(result, test)
#> [1] TRUE

Logic: Read the workbook ranges needed for the challenge; Derive the required intermediate columns; Parse the packed text or string structure; Aggregate or rank the data at the required grouping level.
Strengths: The code maps the workbook rule into a compact, reproducible pipeline.
Areas for Improvement: The solution assumes the workbook layout and selected ranges remain stable, so any structural change in the sheet would require small adjustments.
Gem: The elegant part is how little code is needed once the correct intermediate representation is chosen.

import pandas as pd

path = "655 Increasing or Decreasing or None Sequences.xlsx"
input = pd.read_excel(path, usecols="A", nrows=8)
test = pd.read_excel(path, usecols="B", nrows=8)

input['rn'] = input.index + 1
input = input.assign(Sequences=input['Sequences'].str.split(',')).explode('Sequences').astype({'Sequences': int})
input['diff'] = input.groupby('rn')['Sequences'].diff()

result = input.dropna().groupby('rn').apply(
    lambda x: pd.Series({'Answer Expected': 'I' if all(x['diff'] > 0) else 'D' if all(x['diff'] < 0) else 'N'})
).reset_index()

result = result.drop(columns='rn')

print(result.equals(test))

The Python version follows the same grouped logic and keeps the transformation explicit in a dataframe pipeline.

Difficulty Level

Easy / Medium

The business rule is clear, though the workbook still needs a few transformation steps to reach the expected output.